﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections.Specialized;
using System.Collections;
using System.Data.SqlClient;

namespace SF.Base.Core {
	/// <summary>
	/// 词汇表操作类
	/// </summary>
	public class VocabularyRules {

		private static Dictionary<string, long> StrToNum = new Dictionary<string, long>();
		private static Dictionary<long, string> NumToStr = new Dictionary<long, string>();

		public static string GetStrId(long vid) {
			if (!NumToStr.ContainsKey(vid)) {
				TaxonomyEntity Taxonomy = TaxonomyRules.GetTaxonomy(" where tid = " + vid);
				if (Taxonomy != null) {
					NumToStr.Add(vid, Taxonomy.TaxonomyId);
				}
				else {
					return "";
				}
			}
			return NumToStr[vid];
		}

		public static long GetNumId(string vocabularyId) {
			if (!StrToNum.ContainsKey(vocabularyId)) {
				TaxonomyEntity Taxonomy = TaxonomyRules.GetTaxonomy(" where taxonomy_id = '" + vocabularyId + "'");
				if (Taxonomy != null) {
					StrToNum.Add(vocabularyId, Taxonomy.TID);
				}
				else {
					return 0;
				}
			}

			return StrToNum[vocabularyId];
		}

		public static int Edit(VocabularyEntity vocabulary) {

			using (SqlConnection conn = new SqlConnection(BaseSettings.Instance.ConnSF)) {
				conn.Open();
				SqlTransaction trans = conn.BeginTransaction();
				int ret;
				try {
					ret = Sh.ExecuteNonQuery(
						trans,
						CommandType.Text,
						@" 
							--修改词汇表名称 描述
							update dbo.dt_term_taxonomy
							set 
								[name] = @name,
								[description] = @description,
								status = @status,
								parent = @parent
							where taxonomy_id = @taxonomy_id

						",
						Sh.GetParameter("@name", DbType.String, vocabulary.Name),
						Sh.GetParameter("@slug", DbType.String, vocabulary.Slug),
						Sh.GetParameter("@taxonomy", DbType.String, "vocabulary"),
						Sh.GetParameter("@description", DbType.String, vocabulary.Description),
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabulary.VocabularyId),
						Sh.GetParameter("@user_id", DbType.String, vocabulary.UserId),
						Sh.GetParameter("@status", DbType.Int32, vocabulary.Status),
						Sh.GetParameter("@parent", DbType.String, vocabulary.ParentId)
					);

					if (ret != 1) {
						trans.Rollback();
						return -160301;
					}

					//添加新增的单词
					List<DictEntity> NewDictList = vocabulary.DictList.FindAll(delegate(DictEntity d){return d.EditType == "insert";});

					StringBuilder sb = new StringBuilder();
					int index = 0;	//参数索引
					int count = 0;	//参数个数
					IDataParameter[] idps = new IDataParameter[NewDictList.Count * 4];
					foreach (DictEntity d in NewDictList) {
						sb.AppendFormat(
						@"	
							insert into dt_dicts (
								dict_id,initial,aspell
							) 
							values(
								@dict_id{0},@initial{0},@aspell{0}
							)

							update dt_dicts 
								set apronunciation = (
									select top 1
										apronunciation 
									from dt_dicts 
									where 
										aspell = @aspell{0} 
										and parent = '00000000-0000-0000-0000-000000000000'
								)
							
							
							insert into dt_term_relationships (
								[object_id],taxonomy_id,relation_order,relation_date,status
							) 
							values(
								@dict_id{0},@taxonomy_id{0}," + d.RelationOrder + @",getdate(),1
							)
						", index);
						idps[count++] = Sh.GetParameter(string.Format("@dict_id{0}", index), DbType.String, d.DictId);
						idps[count++] = Sh.GetParameter(string.Format("@initial{0}", index), DbType.String, d.ASpell.Substring(0, 1));
						idps[count++] = Sh.GetParameter(string.Format("@aspell{0}", index), DbType.String, d.ASpell);

						idps[count++] = Sh.GetParameter(string.Format("@taxonomy_id{0}", index), DbType.String, vocabulary.VocabularyId);

						index++;
					}
					if (NewDictList.Count > 0) {
						ret = Sh.ExecuteNonQuery(
							trans,
							CommandType.Text,
							sb.ToString(),
							idps
						);
					}
					

					//删除单词
					List<DictEntity> DelDictList = vocabulary.DictList.FindAll(delegate(DictEntity d) { return d.EditType == "delete"; });

					sb = new StringBuilder();
					index = 0;
					count = 0;
					idps = new IDataParameter[DelDictList.Count * 2];
					foreach (DictEntity d in DelDictList) {
						sb.AppendFormat(
							@"	
								update dt_term_relationships 
								set status = 0 
								where 
									[object_id] = @dict_id{0}
									and taxonomy_id = @taxonomy_id{0}
							", 
							index
						);
						idps[count++] = Sh.GetParameter(string.Format("@dict_id{0}", index), DbType.String, d.DictId);
						idps[count++] = Sh.GetParameter(string.Format("@taxonomy_id{0}", index), DbType.String, vocabulary.VocabularyId);

						index++;
					}
					
					if (DelDictList.Count > 0) {
						ret = Sh.ExecuteNonQuery(
							trans,
							CommandType.Text,
							sb.ToString(),
							idps
						);
					}

					//还原单词
					List<DictEntity> UpdateDictList = vocabulary.DictList.FindAll(delegate(DictEntity d) { return d.EditType == "update"; });

					sb = new StringBuilder();
					index = 0;
					count = 0;
					idps = new IDataParameter[UpdateDictList.Count * 2];
					foreach (DictEntity d in UpdateDictList) {
						sb.AppendFormat(
							@"	
								update dt_term_relationships 
								set status = 1 , relation_order = "+ d.RelationOrder + @"
								where 
									[object_id] = @dict_id{0}
									and taxonomy_id = @taxonomy_id{0}
							",
							index
						);
						idps[count++] = Sh.GetParameter(string.Format("@dict_id{0}", index), DbType.String, d.DictId);
						idps[count++] = Sh.GetParameter(string.Format("@taxonomy_id{0}", index), DbType.String, vocabulary.VocabularyId);

						index++;
					}

					if (UpdateDictList.Count > 0) {
						ret = Sh.ExecuteNonQuery(
							trans,
							CommandType.Text,
							sb.ToString(),
							idps
						);
					}

					string PostContent = string.Empty;

					PostContent = "<div class='title'><span class='time'>" + DateTime.Now.ToString("HH:mm") + "</span>修改<a href='/voc.aspx?vid=" + vocabulary.Vid + "'><span class='spell'>" + vocabulary.Name + "</span></a></div>";
					PostContent += "<div class='content'>" + vocabulary.Description + "</div>";

					ret = Sh.ExecuteNonQuery(
						trans,
						CommandType.Text,
						@"
							insert into dt_posts (
								content,post_date,object_id,object_type,user_id
							)
							values(
								@post_content,getdate(),@object_id,@object_type,@user_id
							)
						",
						Sh.GetParameter("@object_type", DbType.String, ObjectType.Vocabulary),
						Sh.GetParameter("@post_content", DbType.String, PostContent),
						Sh.GetParameter("@user_id", DbType.String, vocabulary.UserId),
						Sh.GetParameter("@object_id", DbType.String, vocabulary.VocabularyId)
					);

					if (ret != 1) {
						trans.Rollback();
						return -160301;
					}
					trans.Commit();
					return 0;
				}
				catch (System.Exception e) {
					Logger.Exception("编辑词汇表失败", e);
					trans.Rollback();
					return -160301;
				}
			}
		}

		public static int Insert(VocabularyEntity vocabulary) {

			using (SqlConnection conn = new SqlConnection(BaseSettings.Instance.ConnSF)) {
				conn.Open();
				SqlTransaction trans = conn.BeginTransaction();

				try {
					object ret = Sh.ExecuteScalar(
						trans,
						CommandType.Text,
						@" 
							insert into dbo.dt_term_taxonomy ( taxonomy_id,[name],slug,[user_id],taxonomy,[description],parent,status)
							values( @taxonomy_id,@name,@slug,@user_id,@taxonomy,@description,@parent,@status ) 
							select @@IDENTITY ;
							
						",
						Sh.GetParameter("@name", DbType.String, vocabulary.Name),
						Sh.GetParameter("@slug", DbType.String, vocabulary.Slug),
						Sh.GetParameter("@taxonomy", DbType.String, "vocabulary"),
						Sh.GetParameter("@description", DbType.String, vocabulary.Description),
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabulary.VocabularyId),
						Sh.GetParameter("@user_id", DbType.String, vocabulary.UserId),
						Sh.GetParameter("@status", DbType.Int32, vocabulary.Status),
						Sh.GetParameter("@parent", DbType.String, vocabulary.ParentId)
					);

					if (ret == null) {
						trans.Rollback();
						return -160301;
					}

					int vid = int.Parse(ret.ToString());

					List<DictEntity> DictList = vocabulary.DictList;

					StringBuilder sb = new StringBuilder();
					int index = 0;
					int count = 0;
					IDataParameter[] idps = new IDataParameter[DictList.Count * 4];
					foreach (DictEntity d in DictList) {
						sb.AppendFormat(
						@"	insert into dt_dicts (
								dict_id,initial,aspell
							) 
							values(
								@dict_id{0},@initial{0},@aspell{0}
							)

							update dt_dicts 
								set apronunciation = (
									select top 1
										apronunciation 
									from dt_dicts 
									where 
										aspell = @aspell{0} 
										and parent = '00000000-0000-0000-0000-000000000000'
								)

							insert into dt_term_relationships (
								[object_id],taxonomy_id,relation_order,relation_date
							) 
							values(
								@dict_id{0},@taxonomy_id{0}," + (index + 1) + @",getdate()
							)
						", index);
						idps[count++] = Sh.GetParameter(string.Format("@dict_id{0}", index), DbType.String, d.DictId);
						idps[count++] = Sh.GetParameter(string.Format("@initial{0}", index), DbType.String, d.ASpell.Substring(0, 1));
						idps[count++] = Sh.GetParameter(string.Format("@aspell{0}", index), DbType.String, d.ASpell);

						idps[count++] = Sh.GetParameter(string.Format("@taxonomy_id{0}", index), DbType.String, vocabulary.VocabularyId);

						index++;
					}

					int line0 = Sh.ExecuteNonQuery(
						trans,
						CommandType.Text,
						sb.ToString(),
						idps
					);

					string PostContent = string.Empty;

					PostContent = "<div class='title'><span class='time'>" + DateTime.Now.ToString("HH:mm") + "</span>创建<a href='/voc.aspx?vid=" + vid + "'><span class='spell'>" + vocabulary.Name + "</span></a></div>";
					PostContent += "<div class='content'>" + vocabulary.Description + "</div>";


					int line1 = Sh.ExecuteNonQuery(
						trans,
						CommandType.Text,
						@"
							insert into dt_posts (
								content,post_date,object_id,object_type,user_id
							)
							values(
								@post_content,getdate(),@object_id,@object_type,@user_id
							)
						",
						Sh.GetParameter("@object_type", DbType.String, ObjectType.Vocabulary),
						Sh.GetParameter("@post_content", DbType.String, PostContent),
						Sh.GetParameter("@user_id", DbType.String, vocabulary.UserId),
						Sh.GetParameter("@object_id", DbType.String, vocabulary.VocabularyId)
					);

					if (line1 != 1) {
						trans.Rollback();
						return -160301;
					}
					trans.Commit();
					vocabulary.Vid = vid;
					return 0;
				}
				catch (System.Exception e) {
					Logger.Exception("创建词汇表失败", e);
					trans.Rollback();
					return -160301;
				}
			}
		}

		/// <summary>
		/// 根据单词拼写查询其归属词汇表
		/// </summary>
		/// <param name="dictId"></param>
		/// <returns></returns>
		public static List<VocabularyEntity> GetVocabularyBySpell(string spell) {
			List<VocabularyEntity> Vocabularys = new List<VocabularyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								taxonomy_date,
								parent,
								status
							from dt_term_taxonomy 
							where taxonomy = 'vocabulary' 
							and taxonomy_id in (
								select 
									taxonomy_id 
								from dt_term_relationships
								where object_id in {
									select
										dict_id
									from dt_dicts
									where aspell = @aspell
								}
							)
						",
						Sh.GetParameter("@aspell", DbType.String, spell)
					)
				) {
					while (dr.Read()) {
						VocabularyEntity Vocabulary = new VocabularyEntity();

						Vocabulary.Vid = dr.GetLong("tid");
						Vocabulary.Name = dr.GetStr("name");
						Vocabulary.Slug = dr.GetStr("slug");
						Vocabulary.VocabularyId = dr.GetStr("taxonomy_id");
						Vocabulary.UserId = dr.GetStr("user_id");
						Vocabulary.Description = dr.GetStr("description");
						Vocabulary.ParentId = dr.GetStr("parent");
						Vocabulary.CreateDate = dr.GetDate("taxonomy_date");

						Vocabularys.Add(Vocabulary);
					}

					return Vocabularys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		/// <summary>
		/// 根据单词查询其归属词汇表
		/// </summary>
		/// <param name="dictId"></param>
		/// <returns></returns>
		public static VocabularyEntity GetVocabularyByDictId(string dictId) {
			VocabularyEntity Vocabulary = null;

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								taxonomy_date,
								parent,
								status
							from dt_term_taxonomy 
							where taxonomy = 'vocabulary' 
							and taxonomy_id in (
								select 
									taxonomy_id 
								from dt_term_relationships
								where object_id = @object_id
							)
						",
						Sh.GetParameter("@object_id", DbType.String, dictId)
					)
				) {
					if (dr.Read()) {
						Vocabulary = new VocabularyEntity();
						Vocabulary.Vid = dr.GetLong("tid");
						Vocabulary.Name = dr.GetStr("name");
						Vocabulary.Slug = dr.GetStr("slug");
						Vocabulary.VocabularyId = dr.GetStr("taxonomy_id");
						Vocabulary.UserId = dr.GetStr("user_id");
						Vocabulary.Description = dr.GetStr("description");
						Vocabulary.ParentId = dr.GetStr("parent");
						Vocabulary.CreateDate = dr.GetDate("taxonomy_date");
					}

					return Vocabulary;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		/// <summary>
		/// 更新词汇表状态
		/// </summary>
		public static int UpdateVocabularyStatus(long vid,int status) {
			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						update dt_term_taxonomy set status = @status where tid = @vid
					",
					Sh.GetParameter("@vid", DbType.Int64, vid),
					Sh.GetParameter("@status", DbType.Int32, status)
				);
				return ret;
			}
			catch (System.Exception e) {
				throw new Exception("更新词汇表状态失败", e);
			}
		}

		public static VocabularyEntity GetVocabulary(long vid) {
			return GetVocabulary(GetStrId(vid));
		}
		public static VocabularyEntity GetVocabulary(string vocabularyId) {
			VocabularyEntity Vocabulary = null;

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								taxonomy_date,
								parent,
								status
							from dt_term_taxonomy 
							where taxonomy_id = @taxonomy_id
							and taxonomy = 'vocabulary'
						",
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
					)
				) {
					if (dr.Read()) {
						Vocabulary = new VocabularyEntity();
						Vocabulary.Vid = dr.GetLong("tid");
						Vocabulary.Name = dr.GetStr("name");
						Vocabulary.Slug = dr.GetStr("slug");
						Vocabulary.VocabularyId = dr.GetStr("taxonomy_id");
						Vocabulary.UserId = dr.GetStr("user_id");
						Vocabulary.Description = dr.GetStr("description");
						Vocabulary.ParentId = dr.GetStr("parent");
						Vocabulary.Status = dr.GetInt("status");
						Vocabulary.CreateDate = dr.GetDate("taxonomy_date");
					}

					return Vocabulary;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		public static int InportParaphrase(string vocabularyId, string fromVocabularyId) {
			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
							update dt_dicts
							set dt_dicts.paraphrase = b.paraphrase,
								dt_dicts.commentate = b.commentate,
								dt_dicts.parent = b.dict_id 
							from dt_dicts, dt_dicts b 
							where dt_dicts.aspell = b.aspell 
								and b.dict_id in (
									select
										object_id
									from dt_term_relationships
									where taxonomy_id = @from_taxonomy_id
								)
								and dt_dicts.dict_id in (
									select
										object_id
									from dt_term_relationships
									where taxonomy_id = @taxonomy_id
								)
								and (
										dt_dicts.paraphrase is null
									)

					",
					Sh.GetParameter("@from_taxonomy_id", DbType.String, fromVocabularyId),
					Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
				);
				return ret;
			}
			catch (System.Exception e) {
				throw new Exception("将单词解释导入新的词汇表", e);
			}
		}

		public static List<VocabularyEntity> GetVocabularysByVocabularyIds(string vocabularyIds) {
			List<VocabularyEntity> Vocabularys = new List<VocabularyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								taxonomy_date,
								parent,
								dict_count = isnull((
									select 
										count(*)
									from dt_term_relationships
									where taxonomy_id = a.taxonomy_id
								),0),
								status
							from dt_term_taxonomy a
							where taxonomy_id in (" + vocabularyIds + @")
							and taxonomy = 'vocabulary'
						"
					)
				) {
					while (dr.Read()) {
						VocabularyEntity Vocabulary = new VocabularyEntity();

						Vocabulary.Vid = dr.GetLong("tid");
						Vocabulary.Name = dr.GetStr("name");
						Vocabulary.Slug = dr.GetStr("slug");
						Vocabulary.VocabularyId = dr.GetStr("taxonomy_id");
						Vocabulary.UserId = dr.GetStr("user_id");
						Vocabulary.Description = dr.GetStr("description");
						Vocabulary.ParentId = dr.GetStr("parent");
						Vocabulary.Status = dr.GetInt("status");
						Vocabulary.CreateDate = dr.GetDate("taxonomy_date");
						Vocabulary.DictCount = dr.GetInt("dict_count");

						Vocabularys.Add(Vocabulary);
					}

					return Vocabularys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		public static List<VocabularyEntity> GetSpecialVocabularysByStationId(string stationId) {

			List<VocabularyEntity> Vocabularys = new List<VocabularyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								a.taxonomy_id,
								user_id,
								taxonomy,
								description,
								taxonomy_date,
								parent,
								status,
								dict_count = isnull((
									select
										count(*)
									from dt_term_relationships c
									where c.taxonomy_id = a.taxonomy_id
								),0)
							from dt_term_taxonomy a
								inner join dt_term_relationships b
									on a.taxonomy_id = b.taxonomy_id
							where b.taxonomy_id = @taxonomy_id
							and a.taxonomy = 'vocabulary'
							and b.object_type = 'specialvocabulary'

							
						",
						Sh.GetParameter("@taxonomy_id", DbType.String, stationId)
					)
				) {
					if (dr.Read()) {
						VocabularyEntity Vocabulary = new VocabularyEntity();

						Vocabulary.Vid = dr.GetLong("tid");
						Vocabulary.Name = dr.GetStr("name");
						Vocabulary.Slug = dr.GetStr("slug");
						Vocabulary.VocabularyId = dr.GetStr("taxonomy_id");
						Vocabulary.UserId = dr.GetStr("user_id");
						Vocabulary.Description = dr.GetStr("description");
						Vocabulary.ParentId = dr.GetStr("parent");
						Vocabulary.Status = dr.GetInt("status");
						Vocabulary.CreateDate = dr.GetDate("taxonomy_date");
						Vocabulary.DictCount = dr.GetInt("dict_count");

						Vocabularys.Add(Vocabulary);
					}

					return Vocabularys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询分类目录失败", e);
			}
		}

		/// <summary>
		/// 查询用户创建的词汇表
		/// </summary>
		/// <param name="userid"></param>
		/// <returns></returns>
		public static List<VocabularyEntity> GetUserCreateVocabularysByUserId(string userid) {

			List<TaxonomyEntity> Taxonomys = TaxonomyRules.GetUserTaxonomysByUserId(userid, "vocabulary");

			List<VocabularyEntity> lists = new List<VocabularyEntity>();

			foreach (TaxonomyEntity Taxonomy in Taxonomys) {
				VocabularyEntity Vocabulary = new VocabularyEntity();
				Vocabulary.Name = Taxonomy.Name;				// 词汇表名称
				Vocabulary.VocabularyId = Taxonomy.TaxonomyId;	// 词汇表ID
				Vocabulary.Description = Taxonomy.Description;	// 词汇表介绍
				Vocabulary.Slug = Taxonomy.Slug;				// 词汇表别名
				Vocabulary.ParentId = Taxonomy.ParentId;		// 词汇表父ID
				Vocabulary.CreateDate = Taxonomy.TaxonomyDate;	// 词汇表创建时间
				Vocabulary.Vid = Taxonomy.TID;

				lists.Add(Vocabulary);
			}

			return lists;
		}

		/// <summary>
		/// 查询用户关注的词汇表
		/// </summary>
		/// <param name="userId"></param>
		/// <returns></returns>
		public static List<VocabularyEntity> GetUserFollowVocabularyByUserId(string userId) {
			List<VocabularyEntity> Vocabularys = new List<VocabularyEntity>();
			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								taxonomy_date,
								parent,
								status,
								follow_count = isnull((
									select 
										count(*)
									from dt_term_taxonomy m 
										inner join dt_term_relationships n
											on m.taxonomy_id = n.taxonomy_id
									where m.taxonomy = 'follow-vocabulary'
									and n.object_id = a.taxonomy_id
								),0)
							from dt_term_taxonomy a
							where taxonomy_id in (
								select 
									object_id
								from dt_term_relationships
								where taxonomy_id in (
									select 
										taxonomy_id 
									from dt_term_taxonomy 
									where taxonomy = 'follow-vocabulary' 
										and user_id = @user_id
								)
							)
						",
						Sh.GetParameter("@user_id", DbType.String, userId)
					)
				) {
					while (dr.Read()) {
						VocabularyEntity Vocabulary = new VocabularyEntity();
						Vocabulary.Vid = dr.GetLong("tid");
						Vocabulary.Name = dr.GetStr("name");
						Vocabulary.Slug = dr.GetStr("slug");
						Vocabulary.VocabularyId = dr.GetStr("taxonomy_id");
						Vocabulary.UserId = dr.GetStr("user_id");
						Vocabulary.Description = dr.GetStr("description");
						Vocabulary.ParentId = dr.GetStr("parent");
						Vocabulary.CreateDate = dr.GetDate("taxonomy_date");
						Vocabulary.FollowCount = dr.GetInt("follow_count");
						Vocabularys.Add(Vocabulary);
					}

					return Vocabularys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询用户关注的分类的关系表失败", e);
			}

		}
		/// <summary>
		/// 查询用户关注的词汇表
		/// </summary>
		public static long[] GetUserFollowVocabularyNumIdsByUserId(string userId) {

			ArrayList al = new ArrayList();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								vid = isnull((
									select
										tid
									from dt_term_taxonomy a
									where a.taxonomy_id = object_id
								),0) 
							from dt_term_relationships
							where taxonomy_id in (
								select 
									taxonomy_id 
								from dt_term_taxonomy 
								where taxonomy = 'follow-vocabulary' 
									and user_id = @user_id
							)
						",
						Sh.GetParameter("@user_id", DbType.String, userId)
					)
				) {
					while (dr.Read()) {
						al.Add(dr.GetLong("vid"));
					}
					long a = 1;
					return (long[])al.ToArray(a.GetType());
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询用户关注的分类的关系表失败", e);
			}
		}

		/// <summary>
		/// 获取全部系统词汇表
		/// </summary>
		/// <returns></returns>
		public static List<VocabularyEntity> GetSystemVocabularys() {
			List<VocabularyEntity> taxonomys = new List<VocabularyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								status,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								parent,
								dict_count = isnull((
									select 
										count(*) 
									from dt_term_relationships c 
									where a.taxonomy_id = c.taxonomy_id
								),0),
								follow_count = isnull((
									select 
										count(*)
									from dt_term_taxonomy m 
										inner join dt_term_relationships n
											on m.taxonomy_id = n.taxonomy_id
									where m.taxonomy = 'follow-vocabulary'
									and n.object_id = a.taxonomy_id
								),0)
							from dt_term_taxonomy a
							where taxonomy = 'vocabulary'
								and user_id = '00000000-0000-0000-0000-000000000000'
						",
						null
					)
				) {
					while (dr.Read()) {
						VocabularyEntity item = new VocabularyEntity();

						item.Vid = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.VocabularyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");

						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");

						item.DictCount = dr.GetInt("dict_count");
						item.FollowCount = dr.GetInt("follow_count");

						taxonomys.Add(item);
					}

					return taxonomys;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询系统某个特定分类列表失败", e);
			}
		}

		public static List<VocabularyEntity> GetSystemVocabularysByStationId(string stationId) {

			List<TaxonomyEntity> Taxonomys = new List<TaxonomyEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								status,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								parent,
								relation_count = isnull((
									select 
										count(*) 
									from dt_term_relationships c 
									where a.taxonomy_id = c.taxonomy_id
								),0)
							from dt_term_taxonomy a
							where taxonomy = 'vocabulary'
								and taxonomy_id in (
									select 
										object_id 
									from dt_term_relationships
									where taxonomy_id = @taxonomy_id
								)
						",
						Sh.GetParameter("@taxonomy_id", DbType.String, stationId)
					)
				) {
					while (dr.Read()) {
						TaxonomyEntity item = new TaxonomyEntity();

						item.TID = dr.GetLong("tid");
						item.Name = dr.GetStr("name");
						item.Slug = dr.GetStr("slug");
						item.Status = dr.GetInt("status");
						item.TaxonomyId = dr.GetStr("taxonomy_id");
						item.UserId = dr.GetStr("user_id");
						item.Taxonomy = dr.GetStr("taxonomy");
						item.Description = dr.GetStr("description");
						item.ParentId = dr.GetStr("parent");

						item.RelationCount = dr.GetInt("relation_count");

						Taxonomys.Add(item);
					}

					List<VocabularyEntity> lists = new List<VocabularyEntity>();

					foreach (TaxonomyEntity Taxonomy in Taxonomys) {

						VocabularyEntity Vocabulary = new VocabularyEntity();
						Vocabulary.Name = Taxonomy.Name;				// 词汇表名称
						Vocabulary.Slug = Taxonomy.Slug;				// 词汇表别名
						Vocabulary.VocabularyId = Taxonomy.TaxonomyId;	// 词汇表ID
						Vocabulary.Description = Taxonomy.Description;	// 词汇表介绍
						Vocabulary.ParentId = Taxonomy.ParentId;		// 词汇表父ID
						Vocabulary.CreateDate = Taxonomy.TaxonomyDate;	// 词汇表创建时间
						Vocabulary.DictCount = Taxonomy.RelationCount;	// 词汇表创建时间
						lists.Add(Vocabulary);

					}

					return lists;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询系统某个特定分类列表失败", e);
			}
		}

		/// <summary>
		/// 获取全部词汇表与单词的关系
		/// </summary>
		///<param name="userId">用户ID</param>
		/// <returns></returns>
		public static List<DictListEntity> GetVocabularyDictLists(string userId) {
			List<TaxonomyEntity> Taxonomys = TaxonomyRules.GetRelationShipsByTaxonomy("vocabulary", userId);
			List<DictListEntity> lists = new List<DictListEntity>();

			foreach (TaxonomyEntity Taxonomy in Taxonomys) {
				DictListEntity DictList = new DictListEntity();

				DictList.VID = Taxonomy.TID;
				DictList.VocabularyId = Taxonomy.TaxonomyId;
				DictList.DictId = Taxonomy.ObjectId;
				DictList.DictOrder = Taxonomy.RelationOrder;

				lists.Add(DictList);
			}

			return lists;
		}

		/// <summary>
		/// 获取某个词汇表
		/// </summary>
		/// <param name="Vid"></param>
		/// <returns></returns>
		public static VocabularyEntity GetVocabularyDictList(string VocabularyId, string userId) {
			VocabularyEntity Vocabulary = new VocabularyEntity();

			List<DictListEntity> DictLists = GetVocabularyDictLists(userId).FindAll(
				delegate(DictListEntity t) { return t.VocabularyId == VocabularyId; }
			);

			List<TaxonomyEntity> Taxonomys = TaxonomyRules.GetRelationShipsByTaxonomy(VocabularyId, userId);

			TaxonomyEntity Taxonomy = Taxonomys.Find(delegate(TaxonomyEntity t) { return t.TaxonomyId == VocabularyId; });

			Vocabulary.Vid = Taxonomy.TID;						// 词汇表数字ID
			Vocabulary.Name = Taxonomy.Name;					// 词汇表名称
			Vocabulary.Slug = Taxonomy.Slug;					// 词汇表别名
			Vocabulary.VocabularyId = Taxonomy.TaxonomyId;		// 词汇表ID
			Vocabulary.Description = Taxonomy.Description;		// 词汇表介绍
			Vocabulary.ParentId = Taxonomy.ParentId;			// 词汇表父ID
			Vocabulary.CreateDate = Taxonomy.TaxonomyDate;		// 词汇表创建时间
			Vocabulary.DictCount = Taxonomy.RelationCount;		// 词汇表创建时间

			List<DictEntity> DictList = new List<DictEntity>();
			List<DictEntity> DictListInDictionary = new List<DictEntity>();

			foreach (DictListEntity dl in DictLists) {
				DictEntity Dict = DictRules.GetDictByDictIdFromCache(dl.DictId);
				DictList.Add(Dict);
				DictEntity DictInDictionary = DictRules.GetDictByDictIdFromCache(Dict.ParentId);
				DictListInDictionary.Add(DictInDictionary);
			}

			Vocabulary.DictListInDictionary = DictListInDictionary;
			Vocabulary.DictList = DictList;

			return Vocabulary;
		}

		/// <summary>
		/// 查询词汇表对应用户身份列表
		/// </summary>
		/// <returns></returns>
		public static StationEntity GetVocabularyStation(int sid) {
			StationEntity Station = null;

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								status,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								parent,
								relation_count = isnull((
									select 
										count(*) 
									from dt_term_relationships c 
									where a.taxonomy_id = c.taxonomy_id
								),0)
							from dt_term_taxonomy a
							where taxonomy = 'station'
								and user_id = '00000000-0000-0000-0000-000000000000'
								and tid = @tid
						",
						Sh.GetParameter("@tid", DbType.Int32, sid)
					)
				) {
					if (dr.Read()) {
						Station = new StationEntity();
						Station.SID = dr.GetLong("tid");
						Station.StationId = dr.GetStr("taxonomy_id");
						Station.Name = dr.GetStr("name");
						Station.Description = dr.GetStr("description");

					}

					return Station;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询系统某个特定分类列表失败", e);
			}
		}

		/// <summary>
		/// 查询词汇表对应用户身份列表
		/// </summary>
		/// <returns></returns>
		public static List<StationEntity> GetVocabularyStation() {
			List<StationEntity> Lists = new List<StationEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								tid,
								name,
								slug,
								status,
								taxonomy_id,
								user_id,
								taxonomy,
								description,
								parent,
								relation_count = isnull((
									select 
										count(*) 
									from dt_term_relationships c 
									where a.taxonomy_id = c.taxonomy_id
								),0)
							from dt_term_taxonomy a
							where taxonomy = 'station'
								and user_id = '00000000-0000-0000-0000-000000000000'
						"
					)
				) {
					while (dr.Read()) {
						StationEntity item = new StationEntity();

						item.SID = dr.GetLong("tid");
						item.StationId = dr.GetStr("taxonomy_id");
						item.Name = dr.GetStr("name");
						item.Description = dr.GetStr("description");

						Lists.Add(item);
					}

					return Lists;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询系统某个特定分类列表失败", e);
			}
		}

		/// <summary>
		/// 查询词汇表分类
		/// 缓存方式查询
		/// </summary>
		/// <returns></returns>
		public static List<StationEntity> GetVocabularyStationFromCache() {
			List<StationEntity> lists = CH.GetListCache<StationEntity>(CH.Keys.STATION);

			if (lists == null) {
				lists = GetVocabularyStation();
				CH.SetListCache<StationEntity>(CH.Keys.STATION, lists);
			}
			return lists;
		}


		/// <summary>
		/// 查询关注词汇表的用户人数
		/// </summary>
		/// <param name="vocabularyid">词汇表文本ID</param>
		/// <returns></returns>
		public static int GetFollowUserCountByVocabularyid(string vocabularyid) {

			List<TaxonomyEntity> Taxonomys = TaxonomyRules.GetRelationShipsByObjectId(vocabularyid, "follow-vocabulary");
			return Taxonomys.Count; ;

		}

		/// <summary>
		/// 查询关注词汇表的用户
		/// </summary>
		/// <param name="vocabularyid">词汇表文本ID</param>
		/// <returns></returns>
		public static List<UserEntity> GetFollowUserByVocabularyid(string vocabularyId) {

			List<UserEntity> Users = new List<UserEntity>();
			List<MetaEntity> Metas = new List<MetaEntity>();

			try {
				using (
					IDataReader dr = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								uid,user_id,login,pass,nicename,realname,email,url,registered,activationkey,status
							from dt_users 
							where user_id in (
								select 
									a.user_id
								from dt_term_taxonomy a inner join dt_term_relationships b
								on a.taxonomy_id = b.taxonomy_id
								where taxonomy = 'follow-vocabulary'
									and b.object_id = @object_id
							)

							select 
								metakey,metavalue,object_id
							from dt_object_meta 
							where metatype = @metatype and object_id in (
								select 
									a.user_id
								from dt_term_taxonomy a inner join dt_term_relationships b
								on a.taxonomy_id = b.taxonomy_id
								where taxonomy = 'follow-vocabulary'
									and b.object_id = @object_id
							)
						",
						Sh.GetParameter("@object_id", DbType.String, vocabularyId),
						Sh.GetParameter("@metatype", DbType.String, MetaType.User)
					)
				) {
					while (dr.Read()) {
						UserEntity item = new UserEntity();

						item.UID = dr.GetLong("uid");
						item.UserId = dr.GetStr("user_id");
						item.Login = dr.GetStr("login");
						item.Pass = dr.GetStr("pass");
						item.NiceName = dr.GetStr("nicename");
						item.RealName = dr.GetStr("realname");

						item.Email = dr.GetStr("email");
						item.Url = dr.GetStr("url");
						item.Registered = dr.GetDate("registered");
						item.ActivationKey = dr.GetStr("activationkey");
						item.Status = dr.GetInt("status");

						Users.Add(item);
					}

					dr.NextResult();

					while (dr.Read()) {
						MetaEntity Meta = new MetaEntity();
						Meta.ObjectId = dr.GetStr("object_id");
						Meta.Key = dr.GetStr("metakey");
						Meta.Value = dr.GetStr("metavalue");
						Metas.Add(Meta);
					}

					foreach (UserEntity u in Users) {
						List<MetaEntity> ms = Metas.FindAll(delegate(MetaEntity m) { return m.ObjectId == u.UserId; });
						StringDictionary sd = new StringDictionary();
						foreach (MetaEntity m in ms) {
							sd.Add(m.Key, m.Value);
							if (string.Equals(m.Key, "avatar", StringComparison.OrdinalIgnoreCase)) {
								u.Avatar = m.Value;
							}
						}
						u.UserMeta = sd;
					}
					return Users;
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询与某对象有关系的关系列表失败", e);
			}
		}

		/// <summary>
		/// 用户关注词汇表
		/// </summary>
		/// <param name="userid"></param>
		/// <param name="vocabularyid"></param>
		/// <returns></returns>
		public static int Follow(string userId, string vocabularyId) {
			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						declare @guid uniqueidentifier
						select @guid = taxonomy_id from dt_term_taxonomy  where user_id = @user_id and taxonomy = 'follow-vocabulary';
						if (@guid is null)
						begin
							
							set @guid = newid()
							insert into dbo.dt_term_taxonomy (
								taxonomy,[name],[slug],[description],taxonomy_id,[user_id],parent,taxonomy_order,taxonomy_date,[status]
							)
							values(
								'follow-vocabulary','关注词汇表',null,'关注词汇表',@guid,@user_id,null,0,getdate(),1
							)
							
						end

						if not exists ( select 1 from dt_term_relationships where object_id = @object_id and taxonomy_id = @guid )
						begin
							insert into dt_term_relationships (
								[object_id],taxonomy_id,relation_order,relation_date
							)
							values(
								@object_id,@guid,0,getdate()
							) 
						end
						else
						begin
							delete dt_term_relationships where object_id = @object_id and taxonomy_id = @guid
						end
						
					",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@object_id", DbType.String, vocabularyId)
				);
				return 0;
			}
			catch (System.Exception e) {
				Logger.Exception("用户关注词汇表失败", e);
				return -160101;
			}
		}

		/// <summary>
		/// 判断用户是否关注此词汇表
		/// </summary>
		/// <param name="userId"></param>
		/// <param name="vocabularyId"></param>
		/// <returns></returns>
		public static Boolean IsFollow(string userId, string vocabularyId) {

			try {
				object o = Sh.ExecuteScalar(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@"
						select 
							trid
						from dt_term_taxonomy a inner join dt_term_relationships b
						on a.taxonomy_id = b.taxonomy_id
						where taxonomy = 'follow-vocabulary'
							and b.object_id = @object_id
							and a.user_id = @user_id
					",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@object_id", DbType.String, vocabularyId)
				);
				if (o == null) {
					return false;
				}
				return true;
			}
			catch (System.Exception e) {
				Logger.Exception("判断用户是否关注此词汇表失败", e);
				return false;
			}
		}

		/// <summary>
		/// 查询某个词汇表每个字母开头单词的个数
		/// Vid Initial Count
		/// 1	A	      47
		/// </summary>
		///<param name="vocabularyId">词汇表ID 0F53DCFD-2053-46A6-ACFA-432A9E4B2AFF</param>
		/// <returns></returns>
		public static List<DictInitialListEntity> GetInitialCount(string vocabularyId) {
			List<DictInitialListEntity> lists = new List<DictInitialListEntity>();

			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								b.initial,
								count(*) as [count]
							from dt_term_relationships a inner join dt_dicts b
							on a.object_id = b.dict_id
							where taxonomy_id = @taxonomy_id
							group by b.initial
						",
						Sh.GetParameter("@taxonomy_id", DbType.String, vocabularyId)
					)
				) {
					while (dataReader.Read()) {
						DictInitialListEntity item = new DictInitialListEntity();

						item.Initial = Sh.GetStr(dataReader, "initial");
						item.Count = Sh.GetInt(dataReader, "count");
						lists.Add(item);
					}
				}
			}
			catch (System.Exception e) {
				throw new Exception("查询某个词汇表每个字母开头单词的个数失败", e);
			}
			return lists;
		}
	}
}
